<?php
/**
* @name Maiji
* @author pd87@qq.com
* @copyright m-j.cc
* @version v1.0
* 2018年3月30日
*/
namespace MAIJI\Db;

use PDO;
use PDOStatement;
use MAIJI\Log;
use MAIJI\Cache;

abstract class Connection
{
	// pdo实例
	protected $pdodb;
	// 最后一次查询的sql
	protected $getsql;
	// 记录 select 和 find
	protected $number;
	// 事物
	protected $thing = true;
	// 表名
	protected $table = '';
	// sql语句
	protected $sql = [];
	// 基本配置
	protected $config = [
		// 数据库类型
		'type' => 'mysql',
		// 服务器地址
		'hostname' => '',
		// 数据库名
		'database' => '',
		// 用户名
		'username' => '',
		// 密码
		'password' => 3306,
		// 端口
		'hostport' => '',
		// 编码
		'charset' => 'utf-8',
		// 表前缀
		'prefix' => '',
		// 长连接
		'pconnect' => true,
	];

	/* 
		读取配置信息
	*/
	public function __construct(array $config = []){
		if(!class_exists('PDO')){
			die('找不到PDO库...');
		}
		if(empty($config)){
			$this -> _ini();
		}else{
			$this->config = array_merge($this->config, $config);
		}
		if(empty($this -> pdodb)){
			$this -> _delsql();
			$this -> pdodb = $this -> _connect();
		}
	}
	
	/* 
		返回对象
	*/
	public function get_obj(){
		return $this;
	}

	/* 
		执行sql语句
	*/
	public function query($sql = ''){
		$ls = 'INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|REVOKE|LOCK|UNLOCK';
		if(preg_match('/^\s*"?('.$ls .')\s+/i',$sql)){
			return $this->_inset($sql);
		}else{
			return $this->_query($sql);
		}
	}
	
	/* 
		where强大组合
	*/
	public function where($where){
		$condition = null;
		isset($this -> sql['where']) ? $this -> sql['where'] : $this -> sql['where'] = null;
		$param = func_get_args();
		if(is_array($where)){
			foreach($where as $k => $v) {
				$ls = explode(' ',trim($v));
				if(in_array(trim($ls[0]),['>','<','>=','<=','!=','<>','LIKE','EQ','NEQ','GT','EGT','LT','ELT'])){
					$condition .= ' ('.$this->_addchar($k).' '.$ls[0].' "'.$ls[1].'") ';
				}else{
					$condition .= ' ('.$this->_addchar($k).'="'.$v.'") ';
				}
				$condition .= 'AND';
			}
			$this -> sql['where'] .= ' WHERE '.substr($condition,0,-3);
		}elseif(is_string($where) && count($param) == 1){
			$this -> sql['where'] .= ' WHERE '.$where;
		}elseif(count($param) >= 2){
			$i=0;
			foreach($param as $k => $v){
				$i = $i + 1;
				if(strtoupper(trim($v)) == 'OR'){
					if(substr($condition,-3) == 'AND'){
						$condition = substr($condition,0,-3).' OR ';
					}else{
						$condition .= ' OR ';
					}
					$i=0;
				}elseif(in_array(strtoupper(trim($v)),['=','>','<','>=','<=','!=','<>','EQ','NEQ','GT','EGT','LT','ELT','LIKE'])){
					$condition .= ' ('.$this->_addchar($param[$k-1]).' '.$v.' "'.$param[$k+1].'") AND';
					$i=0;
					break;
				}else{					
					if($i % 2 == 0){
						$condition .= ' ('.$this->_addchar($param[$k-1]).' = "'.$param[$k].'") AND';
						$i=0;
					}
				}
			}
			$this -> sql['where'] .= ' WHERE '.substr($condition,0,-3);
		}
		return $this;
	}
	
	public function whereor($where){
		$condition = null;
		isset($this -> sql['where']) ? $this -> sql['where'] : $this -> sql['where'] = null;
		$param = func_get_args();
		if(is_array($where)){
			foreach($where as $k => $v) {
				$ls = explode(' ',trim($v));
				if(in_array(trim($ls[0]),['>','<','>=','<=','!=','<>','LIKE','EQ','NEQ','GT','EGT','LT','ELT'])){
					$condition .= ' ('.$this->_addchar($k).' '.$ls[0].' "'.$ls[1].'") ';
				}else{
					$condition .= ' ('.$this->_addchar($k).'="'.$v.'") ';
				}
				$condition .= 'AND';
			}
			$this -> sql['where'] .= ' OR '.substr($condition,0,-3);
		}elseif(is_string($where) && count($param) == 1){
			$this -> sql['where'] .= ' OR '.$where;
		}elseif(count($param) >= 2){
			$i=0;
			foreach($param as $k => $v){
				$i = $i + 1;
				if(in_array(strtoupper(trim($v)),['=','>','<','>=','<=','!=','<>','EQ','NEQ','GT','EGT','LT','ELT','LIKE'])){
					$condition .= ' ('.$this->_addchar($param[$k-1]).' '.$v.' "'.$param[$k+1].'") AND';
					$i=0;
					break;
				}else{
					if($i % 2 == 0){
						$condition .= ' ('.$this->_addchar($param[$k-1]).' = "'.$param[$k].'") AND';
						$i=0;
					}
				}
			}
			$this -> sql['where'] .= ' OR '.substr($condition,0,-3);
		}
		return $this;
	}
	
	/* 
		查询字段
	*/
	public function field($field){
		$param = func_get_args();
		if(count($param) >= 2){
			$field = $param;
		}elseif(is_string($field)){
			$field = explode(',', $field);
		}
		$field = array_map(array($this,'_addchar'),$field);
		$this -> sql['field'] = implode(',', $field);
		return $this;
	}
	
	/* 
		查询条数
		limit('0,10')显示 0 - 10条
		limit('1','10')显示第一页 十条数据
	*/
	public function limit($page,$pagesize = null){
		if($pagesize === null){
			$this -> sql['limit'] = 'limit '.$page;
		}else{
			$pages = intval(($page -1) * $pagesize);
			$this -> sql['limit'] = 'limit '.$pages.','.$pagesize;
		}
		return $this;
	}
	
	/* 
		排序
	*/
	public function order($order){
		$this -> sql['order'] = ' ORDER BY ';
		$param = func_get_args();
		if(count($param) >= 2){
			foreach($param as $k => $v){
				if($k % 2 == 0){
					$this -> sql['order'] .= $this->_addchar($v).' '.$param[$k+1].',';
				}
			}
			$this -> sql['order'] = substr($this -> sql['order'],0,-1);
		}elseif(is_string($order)){
			$this -> sql['order'] .= $order;
		}elseif(is_array($order)){
			foreach($order as $k => $v){
				$this -> sql['order'] .= $this->_addchar($k).' '.$v.',';
			}
			$this -> sql['order'] = substr($this -> sql['order'],0,-1);
		}
		return $this;
	}
	
	/* 
		设置表名
	*/
	public function table($bname = ''){
		if($bname != ''){
			$this -> table = $bname;
			$this -> sql['table'] = $this -> config['prefix'].$this -> table;
		}else{
			$this -> _table();
			$this -> sql['table'] = $this -> config['prefix'].$this -> table;
		}
		return $this;
	}
	
	/* 
		单条查询
	*/
	public function find($bname = ''){
		isset($this -> sql['order']) ? : $this -> sql['order'] = null;
		isset($this -> sql['where']) ? : $this -> sql['where'] = null;
		$this -> table($bname);
		$sql = "select ".trim($this -> sql['field'])." from ".trim($this -> config['database']).'.'.trim($this -> sql['table'])." ".trim($this -> sql['where'])." ".trim($this -> sql['order']).' limit 0,1 ';
		$this -> number = true;
		$this -> _delsql();
		return $this -> _query($sql);
	}
	
	/* 
		多条查询
	*/
	public function select($bname = ''){
		isset($this -> sql['order']) ? : $this -> sql['order'] = null;
		isset($this -> sql['limit']) ? : $this -> sql['limit'] = null;
		isset($this -> sql['where']) ? : $this -> sql['where'] = null;
		$this -> table($bname);
		$sql = "select ".trim($this -> sql['field'])." from ".trim($this -> config['database']).'.'.trim($this -> sql['table'])." ".trim($this -> sql['where'])." ".trim($this -> sql['order'])." ".trim($this -> sql['limit']);
		$this -> number = false;
		$this -> _delsql();
		return $this -> _query($sql);
	}
	
	/* 
		更新
	*/
	public function update($bname=null,$data=null){
		if(is_array($bname)){
			$this -> table();
			$data = $this -> _dataform($this -> sql['table'],$bname);
		}else{
			$this -> sql['table'] = $bname;
			$data = $this -> _dataform($bname,$data);
		}
		if(!isset($this -> sql['where'])){
			\MAIJI\APP_Error::display('更新数据库必须加 where 防止全表更新','错误位置，就是你刚刚写的位置');
			return false;
		}
		if(!$data){
			\MAIJI\APP_Error::display('没有数据，让我如何更新？好好看看官方手册','错误位置，就是你刚刚写的位置');
			return false;
		}
		$tem = [];
		foreach($data as $k => $v){
			$tem[] = $k.'='.$v;
		}
		$temp = implode(',', $tem);
		$sql = "update ".trim($this -> config['database']).'.'.trim($this -> sql['table'])." set ".trim($temp)." ".trim($this -> sql['where']);
		$this -> _delsql();
		return $this -> _inset($sql);
	}
	
	/* 
		插入
	*/
	public function insert($bname=null,$data=null){
		if(is_array($bname)){
			$this -> table();
			$data = $this -> _dataform($this -> sql['table'],$bname);
		}else{
			$this -> sql['table'] = $bname;
			$data = $this -> _dataform($bname,$data);
		}
		if(!$data){
			\MAIJI\APP_Error::display('没有数据，让我如何插入？好好看看官方手册','错误位置，就是你刚刚写的位置');
			return false;
		}
		$sql = "insert into ".trim($this -> config['database']).'.'.trim($this -> sql['table'])." (".implode(',',array_keys($data)).") values(".implode(',',array_values($data)).")";
		$this -> _delsql();
		return $this -> _inset($sql);
	}
	
	/* 
		删除
	*/
	public function delete($bname = '') {
		$this -> table($bname);
		if(empty($this -> sql['where'])){
			\MAIJI\APP_Error::display('大哥，我不敢删全部，请加 where','错误位置，就是你刚刚写的位置');
			return false;
		}
		$sql = "delete from ".trim($this -> config['database']).'.'.trim($this -> sql['table'])." ".$this -> sql['where'];
		$this -> _delsql();
		return $this -> _inset($sql);
	}
	
	/* 
		获取最后一次执行的sql
	*/
	public function getsql(){
		return $this -> getsql;
	}
	
	/* 
		开启事物
	*/
	public function transaction(){
		if($this -> thing){
			$this -> pdodb -> setAttribute(PDO::ATTR_AUTOCOMMIT,false);
			$this -> pdodb -> beginTransaction();
			$this -> thing = false;
		}
		return;
	}
	
	/* 
		提交事物
	*/
	public function commit(){
		$result = null;
		if($this -> thing === false){
			$result = $this -> pdodb -> commit();
			$this -> thing = true;
		}
		return $result;
	}
	
	/* 
		事物回滚
	*/
	public function rollback(){
		$result = null;
		if($this -> thing === false){
			$result = $this -> pdodb -> rollback();
			$this -> thing = true;
		}
		return $result;
	}
 
	/**
	* 关闭连接
	* PHP 在脚本结束时会自动关闭连接。
	*/
	public function close() {
		if(!is_null($this -> pdodb)){
			$this -> pdodb = null;
		}
	}
	
	
	/* 
		格式化模型名
	*/
	public function modeldbname($name){
		$ls = humpToLine($name);
		if(substr($ls,0,1) == '_'){
			return substr($ls,1);
		}else{
			return $ls;
		}
	}
	
	/* 
		获取字段名
	*/
	protected function _bfield($bname){
		$cache = new Cache();
		$field = $cache -> get('db_'.$bname);
		if(empty($field)){
			$sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="'.$bname.'" AND TABLE_SCHEMA="'.$this -> config['database'].'"';
			$pdo = $this -> pdodb -> prepare($sql);
			$pdo -> execute();
			$ls = $pdo -> fetchAll(PDO::FETCH_ASSOC);
			$temp = [];
			foreach ($ls as $k => $v) {
				$temp[$v['COLUMN_NAME']] = 1;
			}
			$cache -> set('db_'.$bname,$temp,60*60*5);
			return $temp;
		}
		return $field;
	}
	
	/* 
		格式化修改数据
	*/
	protected function _dataform($bname,$data){
		if(!is_array($data)){
			return [];
		}
		$field = $this ->_bfield($bname);
		$temp = [];
		foreach($data as $k => $v){
			if(!is_scalar($v)){
				continue;
			}
			if(array_key_exists($k,$field)){
				$k = $this -> _addchar($k);
				$ls = substr($v,0,1);
				if(in_array($ls,array('+','-','*','%'))){
					if($ls == '%'){
						$val = $k.'/ '.substr($v,1);
					}else{
						$val = $k.$v;
					}
				}elseif(is_int($v)){ 
					$val = intval($v); 
				}elseif(is_float($v)){ 
					$val = floatval($v); 
				}elseif(preg_match('/^\(\w*(\+|\-|\*|\/)?\w*\)$/i',$v)){
					$val = $v;
				}elseif(is_string($v)){ 
					$val = '"'.addslashes($v).'"';
				}
				$temp[$k] = $val;
			}
		}
		return $temp;
	}
	
	/* 
		设置表名
	*/
	protected function _table($bname = ''){
		if(empty($bname) && empty($this -> table)){
            $name = str_replace('\\', '/', get_called_class());
            $name = basename($name);
			if(!empty($name)){
				$name = $this -> modeldbname($name);
				$this -> table = strtolower($name);
			}
			\MAIJI\APP_Error::display('没有设置表名？你让我怎么执行','错误位置，就是你刚刚写的位置');
		}elseif(!empty($this -> table)){
			return $this;
		}else{
			$this -> table = $bname;
		}
		return $this;
	}
	
	/* 
		清除sql
	*/
	protected function _delsql(){
		$this -> sql = [];
		$this -> sql['field'] = '*';
		$this -> sql['table'] = $this -> table;
	}
	
	/* 
		格式化字段
	*/
	protected function _addchar($value){
		if('*' == $value || false !== strpos($value,'(') || false !== strpos($value,'.') || false !== strpos($value,'`')){
			//如果包含* 或者 使用了sql方法 则不作处理
		}elseif(false === strpos($value,'`')){
			$value = '`'.trim($value).'`';
		}
		return $value; 
	}
	
	/* 
		赋值配置
	*/
	protected function _ini(){
		foreach(C('mysql') as $v => $a){
			$this -> config[$v] = $a;
		}
	}

	/* 
		连接pdo
	*/
	protected function _connect(){
		$dsn = $this -> config['type'].':host='.$this -> config['hostname'].';port='.$this -> config['hostport'].';dbname='.$this -> config['database'];
		if($this -> config['pconnect']){
			$options = [PDO::ATTR_PERSISTENT => true];
		}else{
			$options = [];
		}
		if(C('basic.DEBUG')){
			$GLOBALS['_DEBUG']['mysql_time'] = isset($GLOBALS['_DEBUG']['mysql_time']) ? $GLOBALS['_DEBUG']['mysql_time'] : 0;
			$GLOBALS['_DEBUG']['mysql'][0] = '[ DB ] '.$dsn;
		}
		try{
			$dbh = new PDO($dsn,$this -> config['username'],$this -> config['password'],$options);
			$dbh -> setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
			$dbh -> setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
			$dbh -> exec('SET NAMES '.$this -> config['charset']);
			if(C('basic.DEBUG')){
				$GLOBALS['_DEBUG']['mysql'][1] = '[ DB ] '.'SET NAMES '.$this -> config['charset'];
			}
		}catch(PDOException $e){
			die ("Error!: " . $e->getMessage() . "<br/>");
		}
		return $dbh;
	}

	/* 
		插入
	*/
	protected function _inset($sql){
		if(C('basic.sqllog')){
			log::x($sql,'debug');
		}
		if(C('basic.DEBUG')){
			$t1 = microtime(true);
		}
		$ls = $this -> pdodb -> exec($sql);
		$this -> getsql = $sql;
		\MAIJI\APP_Error::addmsg($sql,1);
		if(C('basic.DEBUG')){
			$GLOBALS['_DEBUG']['mysql'][] = '[ SQL ] '.$sql.' [RunTime: '.round(microtime(true) - $t1,6).'s]';
			$GLOBALS['_DEBUG']['mysql_time'] = $GLOBALS['_DEBUG']['mysql_time'] + round(microtime(true) - $t1,6);
			unset($t1);
		}
		if(substr($sql,0,6) == 'insert'){
			$this -> insetid = $this -> pdodb -> lastInsertId();
			return $this -> insetid;
		}else{
			return $ls;
		}
	}
	
	/* 
		查询
	*/
	protected function _query($sql){
		if(C('basic.sqllog')){
			log::x($sql,'debug');
		}
		if(C('basic.DEBUG')){
			$t1 = microtime(true);
		}
		$pdostmt = $this -> pdodb -> prepare($sql);
		$pdostmt -> execute();
		$result = $pdostmt -> fetchAll(PDO::FETCH_ASSOC);
		if(C('basic.DEBUG')){
			$GLOBALS['_DEBUG']['mysql'][] = '[ SQL ] '.$sql.' [RunTime: '.round(microtime(true) - $t1,6).'s]';
			$GLOBALS['_DEBUG']['mysql_time'] = $GLOBALS['_DEBUG']['mysql_time'] + round(microtime(true) - $t1,6);
		}
		\MAIJI\APP_Error::addmsg($sql,1);
		$this -> getsql = $sql;
		if(count($result) == 1 && $this -> number){
			return $result[0];
		}else{
			return $result;
		}
	}
}